package com.tdam2011;

import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.provider.CallLog.Calls;

import com.tdam2011.entities.Accion;

public class DatabaseHelper extends SQLiteOpenHelper {

	static final String dbName = "grupo7DB";
	static final String accionesTable = "Acciones";
	static final String colID = "AccionID";
	static final String colNumero = "Numero";
	static final String colEmail = "Email";
	static final String colUserWeb = "UserWeb";
	static final String colContactoID = "ContactoID";
	static final String colFecha = "Fecha";
	static final String colTipoAccion = "TipoAccion";
	static final String colRumboAccion = "RumboAccion";
	static final String colCuerpo = "Cuerpo";

	static final String tipoTable = "TiposAcciones";
	static final String colTipoAccionID = "TipoAccionID";
	static final String colDescripcion = "Descripcion";

	static final String viewAcciones = "ViewsAcciones";

	public DatabaseHelper(Context context) {
		super(context, dbName, null, 1);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub

		db.execSQL("CREATE TABLE " + tipoTable + " (" + colTipoAccionID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + colDescripcion
				+ " TEXT);");

		db.execSQL("CREATE TABLE " + accionesTable + " (" + colID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + colTipoAccion
				+ " INTEGER NOT NULL, " + colNumero + " TEXT, " + colEmail
				+ " TEXT, " + colUserWeb + " TEXT, " + colContactoID
				+ " INTEGER, " + colFecha + " INTEGER," + colCuerpo + " TEXT, "
				+ colRumboAccion + " INTEGER," + "FOREIGN KEY ("
				+ colTipoAccion + ") REFERENCES " + tipoTable + " ("
				+ colTipoAccionID + "));");

		db.execSQL("CREATE TRIGGER fk_acctipo_tipoid " + " BEFORE INSERT "
				+ " ON " + accionesTable + " FOR EACH ROW BEGIN"
				+ " SELECT CASE WHEN ((SELECT " + colTipoAccionID + " FROM "
				+ tipoTable + " WHERE " + colTipoAccionID + "=new."
				+ colTipoAccion + " ) IS NULL)"
				+ " THEN RAISE (ABORT,'Foreign Key Violation') END;" + "  END;");

		InsertTipos(db);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + accionesTable);
		db.execSQL("DROP TABLE IF EXISTS " + tipoTable);
		db.execSQL("DROP TRIGGER IF EXISTS fk_acctipo_tipoid");
		db.execSQL("DROP VIEW IF EXISTS " + viewAcciones);
		onCreate(db);
	}

	void AddAccion(Accion acc) {

		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues cv = new ContentValues();

		cv.put(colTipoAccion, acc.getTipoAccionID());
		cv.put(colNumero, acc.getNumero());
		cv.put(colRumboAccion, acc.getRumboAccion());
		cv.put(colContactoID, acc.getContactoID());
		cv.put(colFecha, String.valueOf(acc.getFecha()));
		cv.put(colCuerpo, acc.getCuerpo());
		cv.put(colEmail, acc.getEmail());
		// cv.put(colDept,2);

		db.insert(accionesTable, colTipoAccion, cv);

		db.close();

	}

	Cursor getAllAcciones() {
		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cur = db.rawQuery("SELECT " + colID + " as _id, " + colNumero
				+ ", " + colRumboAccion + ", " + colContactoID + ", "
				+ colTipoAccion + ", " + colFecha + ", " + colCuerpo + ", " + colEmail + " from " + accionesTable + " ORDER BY "
				+ colFecha + " DESC", new String[] {});
		return cur;

	}
	
	Cursor getAccionesPorAccion(String accion) {
		SQLiteDatabase db = this.getWritableDatabase();
		String codAccion;
		if(accion.equals("Call")){
			codAccion = "1";
		}else if (accion.equals("SMS")){
			codAccion = "2";
		}else{
			codAccion = "3";
		}
		Cursor cur = db.rawQuery("SELECT " + colID + " as _id, " + colNumero
				+ ", " + colRumboAccion + ", " + colContactoID + ", "
				+ colTipoAccion + ", " + colFecha + ", " + colCuerpo + ", " + colEmail + " from " + accionesTable + " WHERE " + colTipoAccion + " = " + codAccion + " ORDER BY "
				+ colFecha + " DESC", new String[] {});
		return cur;

	}	
	
	Cursor getAccionesPorAccionPorContacto(String accion, String contactoId) {
		SQLiteDatabase db = this.getWritableDatabase();
		String codAccion;
		if(accion.equals("Call")){
			codAccion = "1";
		}else if (accion.equals("SMS")){
			codAccion = "2";
		}else{
			codAccion = "3";
		}
		
		Cursor cur = db.rawQuery("SELECT " + colID + " as _id, " + colNumero
				+ ", " + colRumboAccion + ", " + colContactoID + ", "
				+ colTipoAccion + ", " + colFecha + ", " + colCuerpo + ", " + colEmail + " from " + accionesTable + " WHERE " + colTipoAccion + " = " + codAccion + " AND " + colContactoID + " = " + contactoId + " ORDER BY "
				+ colFecha + " DESC", new String[] {});
		return cur;

	}
	
	Cursor getAllTipos() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("SELECT " + colTipoAccionID + " as _id, "
				+ colDescripcion + " from " + tipoTable, new String[] {});

		return cur;
	}

	void InsertTipos(SQLiteDatabase db) {
		ContentValues cv = new ContentValues();
		cv.put(colTipoAccionID, 1);
		cv.put(colDescripcion, "Llamar");
		db.insert(tipoTable, colTipoAccionID, cv);
		cv.put(colTipoAccionID, 2);
		cv.put(colDescripcion, "SMS");
		db.insert(tipoTable, colTipoAccionID, cv);
		cv.put(colTipoAccionID, 3);
		cv.put(colDescripcion, "Email");
		db.insert(tipoTable, colTipoAccionID, cv);
		cv.put(colTipoAccionID, 4);
		cv.put(colDescripcion, "Mensaje Service Web");
		db.insert(tipoTable, colTipoAccionID, cv);
	}

	public Cursor getAccByTipo(String Dept) {
		SQLiteDatabase db = this.getReadableDatabase();
		String[] columns = new String[] { "_id", colTipoAccion, colNumero,
				colContactoID };
		Cursor c = db.query(viewAcciones, columns, colTipoAccion + "=?",
				new String[] { Dept }, null, null, null);
		return c;
	}

	public int UpdateAccion(Accion acc) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(colTipoAccion, acc.getTipoAccionID());
		cv.put(colNumero, acc.getNumero());
		cv.put(colContactoID, acc.getContactoID());
		return db.update(accionesTable, cv, colID + "=?",
				new String[] { String.valueOf(acc.getId()) });

	}

	public void DeleteEmp(Accion acc) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(accionesTable, colID + "=?",
				new String[] { String.valueOf(acc.getId()) });
		db.close();
	}

	public void DeleteAllAccion() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(accionesTable, colTipoAccion + " != " + 3,  null);
		db.close();
	}

}
